package dk.mga.androidExplore.gpsTracker;

import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;
import android.location.LocationListener;
import android.location.LocationManager;
import android.os.Bundle;
import android.util.Log;
/**
 * The class that handles all database I/O's.
 * 
 * @author allan
 */
public class DBHandler extends SQLiteOpenHelper implements IDBConstant, LocationListener {

	private static final String tag  = "myLog";
	private String instanceRecording;
	
	public void setRecording(String recording) {
		this.instanceRecording = recording;
	}

	private static DBHandler instance;
	
	/**
	 * Only one instance of a database handler must be available, 
	 * to avoid more than one entrance to the DB.
	 * @param context
	 * @return
	 */
	public static DBHandler singleton(Context context) {
		if (instance == null) {
			instance = new DBHandler(context);
			Log.d(tag, "DBHandler is constructed");
		}
		return instance;
	}
	/**
	 * Private constructor is part of the singleton pattern
	 * @param context
	 */
	private DBHandler(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.d(tag, "Creating the table "+TABLE_NAME);
		db.execSQL("CREATE TABLE "+TABLE_NAME+" ("
				+_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ DATE + " INTEGER, "
				+ RECORDING_IDENTIFIER + " TEXT NOT NULL, "
				+ SPEED + " INTEGER, "
				+ ALTITUDE + " INTEGER, "
				+ LONGITUDE + " INTEGER, "
				+ LATITUDE + " INTEGER, "
				+ ACCURACY + " INTEGER"
				+");");
	}

	/**
	 * The lazy and easy way of handling updates - so far
	 * just delete the old table and create a new.
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVer, int newVer) {
		Log.d(tag, "DB is upgraded");
		db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME);
		onCreate(db);
	}
	
	///////////////////////////////////////////////////////////////
	/**
	 * A quick way to clean the database
	 */
	public void cleanDB() {
		onUpgrade(getWritableDatabase(), 0, -1);
//		getWritableDatabase().close();
	}
	
	/**
	 * Query and return a list of distinct recording identifier
	 * @return list of recording names
	 */
	public ArrayList<String> getRecordings() {
		String[] select= new String[]{"DISTINCT "+RECORDING_IDENTIFIER};
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME, select, null, null, null, null, null);
		ArrayList<String> list = new ArrayList<String>();
		while (cursor.moveToNext()) {
			list.add(cursor.getString(0));
		}
		return list;
	}
	
	private int getQueryResultInt(String rName, String[] select) {
		String where = RECORDING_IDENTIFIER+"=?";
		String[] whereArg = {rName};
		Cursor cursor = getReadableDatabase().query(TABLE_NAME, select, where, whereArg, null, null, null);
		if (cursor.moveToNext()) {
			return cursor.getInt(0);
		}
		return 0;
	}

	private long getQueryResultLong(String rName, String[] select) {
		String where = RECORDING_IDENTIFIER+"=?";
		String[] whereArg = {rName};
		Cursor cursor = getReadableDatabase().query(TABLE_NAME, select, where, whereArg, null, null, null);
		if (cursor.moveToNext()) {
			return cursor.getLong(0);
		}
		return 0;
	}

	public Date getStartTime(String recordingName) {
		String[] select= new String[]{"MIN("+DATE+")"};
		Long l = getQueryResultLong(recordingName, select);
		if (l>0) {
			Date dStart = new Date(l);
			return dStart;
		}
		return null;
	}
	/**
	 * Calculate the difference between the oldest and youngest
	 * and oldest record with the given recording name.
	 * @param recordingName name of recording
	 * @return
	 */
	public int getDurationInSec(String recordingName) {
		Date start = getStartTime(recordingName);
		if (start == null) return 0;
		String[] select= new String[]{"MAX("+DATE+")"};
		Long l = getQueryResultLong(recordingName, select);
		return (int)((l - start.getTime())/1000);
	}
	/**
	 * Find number of rows where the RECORDING_IDENTIFIER
	 * is equal recordingName 
	 * @param recordingName name of recording
	 * @return
	 */
	public int getSamplesCounts(String recordingName) {
		String[] select= new String[]{"COUNT("+RECORDING_IDENTIFIER+")"};
		return getQueryResultInt(recordingName, select);
	}
	
	public float getMaxSpeedKmH(String rName) {
		String[] select= new String[]{"MAX("+SPEED+")"};
		String where = RECORDING_IDENTIFIER+"=?";
		String[] whereArg = {rName};
		Cursor cursor = getReadableDatabase().query(TABLE_NAME, select, where, whereArg, null, null, null);
		if (cursor.moveToNext()) {
			return fromMpStoKMpH(cursor.getFloat(0));
		}
		return 0;
	}
	
	public int getMaxAccuracy(String recordingName) {
		String[] select= new String[]{"MAX("+ACCURACY+")"};
		return getQueryResultInt(recordingName, select);
	}

	public int getMaxAltitude(String recordingName) {
		String[] select= new String[]{"MAX("+ALTITUDE+")"};
		return getQueryResultInt(recordingName, select);
	}

	/**
	 * Helper method to convert from m/s to km/h
	 * with 2 decimals.
	 * @param mPerS
	 * @return
	 */
	public static float fromMpStoKMpH(float mPerS) {
		return Math.round(mPerS*360)/100f;
	}
	
	/**
	 * Remove a recording from the database
	 * @param recording to be removed.
	 */
	public void removeFromDB(String recording) {
		SQLiteDatabase db = getWritableDatabase();
		String where = RECORDING_IDENTIFIER+"=?";
		Log.d(tag, "Delete rows where "+where);
		db.delete(TABLE_NAME, where, new String[] {recording});
	}

	@Override
	public void onLocationChanged(Location location) {
		if (instanceRecording == null) {
			Log.e(tag, "Recording-id not set!");
			return;
		}
		Log.d(tag, "insert in table where recording id="+instanceRecording);
		ContentValues values = new ContentValues();
		values.put(DATE, location.getTime());
		values.put(RECORDING_IDENTIFIER, instanceRecording);
		values.put(SPEED, location.getSpeed());
		values.put(ALTITUDE, location.getAltitude());
		values.put(LONGITUDE, location.getLongitude());
		values.put(LATITUDE, location.getLatitude());
		values.put(ACCURACY, location.getAccuracy());
		Log.d(tag, "values: "+values.toString());
		try {
			getWritableDatabase().insertOrThrow(TABLE_NAME, null, values);
			Log.d(tag, "insert succeded WOW");
		} catch (SQLException e) {
			Log.d(tag, "insert threw an exception values:"+values.toString(), e);
		}
	}
	
	public Iterator<Location> getLocations(final String recording) {

		Iterator<Location> i = new Iterator<Location>() {
			
			private Cursor cursor=null;
			@Override
			public boolean hasNext() {
				if (cursor == null) {
					Log.d(tag, "Cursor is null - let's query Thread name"+Thread.currentThread().getName());
					String where = RECORDING_IDENTIFIER+"=?";
					String[] whereArg = {recording};
					String[] from = {DATE, SPEED, ALTITUDE, LONGITUDE, LATITUDE, ACCURACY};
					String ordered_by = DATE + " ASC";
					String having = null;
					having = ACCURACY+" < 30";
					cursor = getReadableDatabase().query(TABLE_NAME, from, where, whereArg, DATE, having, ordered_by);
					Log.d(tag, "now curser has count # "+ (cursor==null ? "cursor==null" : ""+cursor.getCount()));
				}
				return cursor.moveToNext();
			}

			@Override
			public Location next() {
				Location l = new Location(LocationManager.GPS_PROVIDER);
				l.setTime(cursor.getLong(0));
				l.setSpeed(cursor.getFloat(1));
				l.setAltitude(cursor.getDouble(2));
				l.setLongitude(cursor.getDouble(3));
				l.setLatitude(cursor.getDouble(4));
				l.setAccuracy(cursor.getFloat(5));
				return l;
			}

			@Override
			public void remove() {
				// TODO Auto-generated method stub
				
			}
			
		};
		return i;
	}

	@Override
	public void onProviderDisabled(String provider) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void onProviderEnabled(String provider) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void onStatusChanged(String provider, int status, Bundle extras) {
		// TODO Auto-generated method stub
		
	}
	
}
